﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;

namespace WindowsFormsApplication5
{
    public class DataBase
    {
       private string dir = " "; 
       private string fulldir = " ";
       private string NameDB = "map.mdb";
       private string table = " ";
       private string message = " ";

       OleDbConnection connection;
       OleDbCommand commandSQL;
       OleDbDataReader readSQL;

       public DataBase(string sdir, string table)
       {
           dir = sdir;
           fulldir = Path.Combine(dir, NameDB);
           this.table = table;
       }

        private void OpenDB()
        {

          connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ fulldir);   

        }

        public void InsertDB(int [,] map)
        {
            for (int i = 0; i < map.GetLength(0); i++)
            {
                try
                {
                    OpenDB();
                    commandSQL = new OleDbCommand("INSERT INTO [cs]([x] , [y]) VALUES(@v1 , @v2);", connection);
                    commandSQL.Parameters.AddWithValue("@v1", map[i, 0]);
                    commandSQL.Parameters.AddWithValue("@v2", map[i, 1]);
                    connection.Open();
                    commandSQL.ExecuteNonQuery();
                   
                }
                catch (Exception e)
                {
                    message = "Data Base is not available";
                    ToString();
                }
                finally
                {
                    CloseDB();
                }
            }

        }

        public void ClearDB()
        {
            try
            {
                OpenDB();
                //commandSQL = new OleDbCommand("TRUNCATE TABLE [" + table + "] ;", connection);
                commandSQL = new OleDbCommand("DELETE FROM [" + table + "] WHERE [id] in (SELECT [id] FROM [" + table + "]); ", connection);
                //   Console.WriteLine("DELETE FROM [" + table + "] WHERE [id] in (SELECT [id] FROM [" + table + "]); ");
                connection.Open();
                commandSQL.ExecuteNonQuery();
                
            }
            catch (Exception e)
            {
                message = "Can't delete DataBase";
                ToString();
            }
            finally
            {
                CloseDB();
            }
        }

        public void ReadDB(int [,] coordinates)
        {
            int i = 0;

            try
            {
                OpenDB();
                connection.Open();
                commandSQL = new OleDbCommand("SELECT * FROM [" + table + "];", connection);
                //Console.WriteLine("SELECT * FROM [" + table + "];");
                readSQL = commandSQL.ExecuteReader();

                if (readSQL.HasRows)
                {

                    while (readSQL.Read())
                    {
                        coordinates[i, 0] = Convert.ToInt32(readSQL["x"]);
                        coordinates[i, 1] = Convert.ToInt32(readSQL["y"]);
                        //Console.Write(readSQL["x"] + "     ");
                        //Console.Write(readSQL["y"]);
                        //Console.WriteLine();
                        i++;

                    }

                }
            }
            catch (Exception ex)
            {
                message = "Error of reading DataBase";
                ToString();
            }
            finally
            {
                CloseDB();
            }
        }





            
        private void CloseDB()
        {
            connection.Close();      
        }

        public override string ToString()
        {
            MessageBox.Show(message);
            return "Good";
          
        }
    }
}
